/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sql;

import system.*;
import java.sql.*;
import java.util.ArrayList;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JOptionPane;

/**
 *
 * @author Micke
 */
public class SQLHandler {

    private SQLConnector sql;
    private Connection c;
    private Statement statement;
    private Statement getActorsStatement;
    private Statement getAuthorsStatement;
    private Statement getDirectorsStatement;
    private Statement getProducersStatement;
    private Statement com1;
    private Statement com2;
    private ResultSet rs;
    private boolean connected;

    public SQLHandler() {
        c = null;
        connected = false;
    }

    public boolean Connect() {

        try {
            // get connection to an Oracle database
            c = sql.getConnection();

            statement = c.createStatement();
            getActorsStatement = c.createStatement();
            getAuthorsStatement = c.createStatement();
            getDirectorsStatement = c.createStatement();
            getProducersStatement = c.createStatement();
            com1 = c.createStatement();
            com2 = c.createStatement();

            connected = true;
            System.out.println("Connected to SQL!!!");
            return true;


        } catch (Exception e) {
            // handle the exception
            e.printStackTrace();
            System.exit(1);
        }
        return false;
    }

    public ArrayList<Grade> getGrades() {
        ArrayList<Grade> grades = new ArrayList<Grade>();
        int movieID, userID;
        float grade;
        boolean isFavourite;

        try {
            rs = statement.executeQuery("SELECT * FROM grades;");
            while (rs.next()) {
                movieID = rs.getInt("movieID");
                userID = rs.getInt("userID");
                grade = rs.getFloat("grade");
                isFavourite = rs.getBoolean("isFavourite");

                grades.add(new Grade(movieID, userID, grade, isFavourite));
            }
        } catch (Exception e) {
            System.out.println("error in getGrade statement");
            return null;
        }
        return grades;
    }

    public boolean updateUserGrade(int movieID, int userID, float grade) {
        try {
            String sqlUpdate = "UPDATE grades SET grade=" + grade + " WHERE grades.userID=" + userID + " AND grades.movieID=" + movieID + ";";
            int updateCount = statement.executeUpdate(sqlUpdate);
            //System.out.println("kör statement");
            return true;

        } catch (Exception e) {
            System.out.println(e);
            System.out.println("error in updateUserGrade statement");
        }
        return false;
    }

    public boolean updateAvgGrade(int movieID, float grade) {
        try {
            String sqlUpdate = "UPDATE movie SET grade=" + grade + " WHERE movieID=" + movieID + ";";
            int updateCount = statement.executeUpdate(sqlUpdate);
            return true;

        } catch (Exception e) {
            System.out.println("error in updateAvgGrade statement");
        }
        return false;
    }

    public Grade getAUserGrade(int movieID, int userID) {
        try {
            rs = statement.executeQuery("SELECT * FROM grades WHERE grades.userID=" + userID + " AND grades.movieID=" + movieID + ";");
            while (rs.next()) {

                float grade = rs.getFloat("grade");
                boolean isFavourite = rs.getBoolean("isFavourite");

                return new Grade(movieID, userID, grade, isFavourite);
            }
        } catch (Exception e) {
            System.out.println(e);
            System.out.println("error in getAUserGrade statement");

        }
        return null;
    }

    public boolean setFavourite(int movieID, int userID, boolean isFavourite) {
        try {

            String sqlUpdate = "UPDATE grades SET isFavourite=" + isFavourite + " WHERE userID=" + userID + " AND movieID=" + movieID + ";";
            int updateCount = statement.executeUpdate(sqlUpdate);

            return true;

        } catch (Exception e) {
            System.out.println("error in setFavourite statement");
        }
        return false;
    }

    public boolean getFavourite(int movieID, int userID){
        try {
            rs = statement.executeQuery("SELECT isFavourite FROM grades WHERE grades.userID=" + userID + " AND grades.movieID=" + movieID + ";");
            while (rs.next()) {


                return rs.getBoolean("isFavourite");


            }
        } catch (Exception e) {
            System.out.println(e);
            System.out.println("error in getFavourite statement");

        }
        return false;
    }

    public ArrayList<User> getUsers() {
        ArrayList<User> users = new ArrayList<User>();
        int userID;
        String userName, password;


        try {
            rs = statement.executeQuery("SELECT * FROM user;");
            while (rs.next()) {

                userID = rs.getInt("userID");
                userName = rs.getString("userName");
                password = rs.getString("password");

                users.add(new User(userName, userID, password));
            }
        } catch (SQLException e) {
            System.out.println("sql exception");
        } catch (Exception e) {
            System.out.println("error in getUsers statement");
            System.out.println(e);
            return null;
        }
        return users;
    }

    public String[] getTop10List(){
        String[] titles = new String[10];
        int k = 0;
        try {
            rs = statement.executeQuery("SELECT movie.title FROM movie ORDER BY  movie.grade DESC LIMIT 10;");
            while (rs.next()) {
                titles[k] = rs.getString("title");
                k++;
            }

        } catch (Exception e) {
            System.out.println("error in getTop10List statement");
            return null;
        }
        return titles;
    }

    /**
     * movieID;
    yearOfRelease;
    runtime;
    budget;
    title;
    language;
    imgURL;
    genre;
    cast;
    author;
    director;
    producer;
     * @return
     *
     *
     */
    public ArrayList<Movie> getMovies() {


        ArrayList<Movie> movies = new ArrayList<Movie>();

        int movieID;
        int yearOfRelease;
        int runtime;
        int budget;
        int[] personID;
        String title;
        String language;
        String imgURL;
        String genre;
        String actor;
        String author;
        String director;
        String producer;
        String plot;
        float grade;
        int nameCounter;
        try {
            ResultSet movieRS = statement.executeQuery("SELECT * FROM movie ORDER BY title;");


            while (movieRS.next()) {
                personID = new int[100];
                int i = 0;

                movieID = movieRS.getInt("movieID");
                yearOfRelease = movieRS.getInt("yearOfRelease");
                runtime = movieRS.getInt("runtime");
                budget = movieRS.getInt("budget");
                title = movieRS.getString("title");
                language = movieRS.getString("language");
                imgURL = movieRS.getString("imageURL");
                genre = movieRS.getString("genre");
                plot = movieRS.getString("plot");
                grade = movieRS.getFloat("grade");



                ResultSet actorRS = getActorsStatement.executeQuery("SELECT persons.firstname,persons.lastname,actor.movieID "
                        + "FROM actor,persons WHERE persons.personID = actor.personID AND actor.movieID=" + movieID + ";");
                StringBuilder sb = new StringBuilder();
                nameCounter=0;

                while (actorRS.next()) {


                    if (actorRS.getInt("movieID") == movieID) {

                        if (i != 0) {
                            sb.append(", ");
                        }
                        if(nameCounter==2){
                            sb.append("\n           ");
                        }
                        sb.append(actorRS.getString("firstName"));
                        sb.append(" ");
                        sb.append(actorRS.getString("lastName"));
                        i++;
                        nameCounter++;
                    }
                }
                i = 0;
                actor = sb.toString();
                sb = new StringBuilder();
                nameCounter=0;

                ResultSet authorRS = getAuthorsStatement.executeQuery("SELECT persons.firstname,persons.lastname,author.movieID "
                        + "FROM author,persons WHERE persons.personID = author.personID AND author.movieID=" + movieID + ";");
                while (actorRS.next()) {


                    if (authorRS.getInt("movieID") == movieID) {

                        if (i != 0) {
                            sb.append(", ");
                        }
                        if(nameCounter==2){
                            sb.append("\n                  ");
                        }
                        sb.append(authorRS.getString("firstName"));
                        sb.append(" ");
                        sb.append(authorRS.getString("lastName"));
                        i++;
                        nameCounter++;
                    }
                }

                author = sb.toString();
                System.out.println(sb.toString());
                sb = new StringBuilder();
                nameCounter=0;
                i=0;

                ResultSet directorRS = getDirectorsStatement.executeQuery("SELECT persons.firstname,persons.lastname,director.movieID "
                        + "FROM director,persons WHERE persons.personID = director.personID AND director.movieID=" + movieID + ";");
                while (directorRS.next()) {


                    if (directorRS.getInt("movieID") == movieID) {

                        if (i != 0) {
                            sb.append(", ");
                        }
                        if(nameCounter==2){
                            sb.append("\n           ");
                        }
                        sb.append(directorRS.getString("firstName"));
                        sb.append(" ");
                        sb.append(directorRS.getString("lastName"));
                        i++;
                        nameCounter++;
                    }
                }

                director = sb.toString();
                sb = new StringBuilder();
                nameCounter=0;
                i=0;

                ResultSet producerRS = getProducersStatement.executeQuery("SELECT persons.firstname,persons.lastname,producer.movieID "
                        + "FROM producer,persons WHERE persons.personID = producer.personID AND producer.movieID=" + movieID + ";");
                while (producerRS.next()) {


                    if (producerRS.getInt("movieID") == movieID) {

                        if (i != 0) {
                            sb.append(", ");
                        }
                        if(nameCounter==2){
                            sb.append("\n                   ");
                        }
                        sb.append(producerRS.getString("firstName"));
                        sb.append(" ");
                        sb.append(producerRS.getString("lastName"));
                        i++;
                        nameCounter++;
                    }
                }

                producer = sb.toString();


                movies.add(new Movie(movieID, title, yearOfRelease, runtime,
                        budget, language, genre, actor, author, director,
                        producer, imgURL, plot, grade));

            }
        } catch (NullPointerException ne) {
            System.out.println(ne);
            return null;
        } catch (Exception e) {
            System.out.println(e);
            return null;
        }
        return movies;
    }

    public int getLength(String table) {
        int count = 0;
        try {
            rs = statement.executeQuery("SELECT * FROM " + table + ";");
            while (rs.next()) {
                count++;
            }
        } catch (Exception e) {
            System.out.println("error in getLength statement");
            return -1;
        }
        return count;
    }

    public int getLengthOfFavourites(int userID) {
        int count = 0;
        try {
            
            rs = statement.executeQuery("SELECT isFavourite FROM grades WHERE userID=" + userID + " AND isFavourite=true;");
            while (rs.next()) {
                count++;
            }
            
        } catch (Exception e) {
            System.out.println("error in getLengthOfFavourites statement");
            return -1;
        }
        return count;
    }

    public String[] getUserFavouriteTitles(int userID) {
        String[] titles = new String[getLengthOfFavourites(userID)];
        int k = 0;
        try {
            rs = statement.executeQuery("SELECT movie.title FROM movie,grades WHERE grades.userID =" + userID + " "
                    + "AND grades.isFavourite = true and movie.movieID = grades.movieID ORDER BY movie.title;");
            while (rs.next()) {
                titles[k] = rs.getString("title");
                k++;
            }

        } catch (Exception e) {
            System.out.println("error in getUserFavouriteTitles statement");
            return null;
        }
        return titles;
    }

    public boolean isConnected() {
        return connected;
    }

    public boolean endConnection() {
        try {
            c.close();
            connected = false;
            System.out.println("Disconnected from SQL!!!!");
            return true;
        } catch (Exception ignore) {
            return false;
        }

    }

    public float getAverageGrade(int movieID){
        float grade = 0;
        try {
            rs = statement.executeQuery("SELECT grade FROM movie WHERE movieID=" + movieID + ";");
            while (rs.next()) {
                grade=rs.getFloat("grade");
            }
        } catch (Exception e) {
            System.out.println("error in getLength statement");
            return -1;
        }
        return grade;
    }

    public String getDateTime() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }

    public void addComment(int movieid, int userid, String comment) {
        try {
            String todo = "INSERT INTO `da5221_grp10`.`comments` (`movieid`, `userid`, `datetime`, `comment`) VALUES (" + movieid + ", " + userid + ", '" + getDateTime() + "', '" + comment + "');";
            com1.executeUpdate(todo);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }

    public ResultSet commentsForMovieID(int movieid) {
        try {
            ResultSet commentsRS = com2.executeQuery("SELECT * FROM comments WHERE movieID = " + movieid + " ORDER BY datetime ASC;");
            return commentsRS;
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e);
        }
        return null;
    }

    public int getLengthWhere(String table, String where) {
        int count = 0;
        try {
            rs = statement.executeQuery("SELECT * FROM " + table + " WHERE " + where + ";");
            while (rs.next()) {
                count++;
            }
        } catch (Exception e) {
            System.out.println("error in getLengthWhere statement");
            return -1;
        }
        return count;
    }

    public String getUserNameForUserID(int userID) {
        String username = "";
        String uID = "" + userID;
        try {
            rs = statement.executeQuery("SELECT * FROM user WHERE userID = " + uID + ";");
            while (rs.next()) {
                username = rs.getString("userName");
            }

        } catch (Exception e) {
            System.out.println("error in getUserNameForUserID statement");
            System.out.println(e);
            return null;
        }
        return username;
    }

    
}
